/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package net.hnilice.excel;

import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Collection;
import java.util.Date;
import java.util.Set;
import java.util.TreeSet;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author swosh
 */
public class TerminDAO {

    private Logger log = Logger.getLogger(getClass().getName());

    public TerminDAO() {
    }

    /**
     * @param termin
     * @param collTermin
     * @return
     * @throws TerminDAOException
     */
    public boolean saveTermin(TerminCisOri termin) {
        //TODO pool
        if (termin.getNazAkce() == null || termin.getNazUlice() == null ||
                termin.getTerCis() == null || termin.getCisOrientacni() == null) {
            return false;
        }
        Connection connection = null;
        try {
            connection = ConnectionFactoryMyEdit.getConnection();
        } catch (SQLException ex) {
            log.log(Level.SEVERE, null, ex);
        }
        int numInserted;
        PreparedStatement ps = null;
        boolean ok = true;
        String nazUlice = termin.getNazUlice();
        Date terCis = termin.getTerCis();
        String nazAkce = termin.getNazAkce();
        int[] cisOrientacni = termin.getCisOrientacni();
        Array cisOrientacniIA = new IntArray(cisOrientacni);


        Timestamp terCisTS = new java.sql.Timestamp(terCis.getTime());
        //TODO INSERT into cis_orientacni
        String query = "INSERT INTO termin (naz_ulice, termin_cisteni, naz_akce, cis_orientacni) VALUES (?, ?, ?, ?)";
        /*String queryUpdate = UPDATE termin SET termin = ? WHERE naz_ulice = ? AND naz_akce = ? AND  cis_orientacni = ?;
        String querySelect = SELECT naz_ulice, naz_akce, cis_orientacni FROM termin WHERE naz_ulice = ? AND naz_akce = ? AND  cis_orientacni = ?)
         */
        try {
            ps = connection.prepareStatement(query);
            ps.setString(1, nazUlice);
            ps.setTimestamp(2, terCisTS);
            ps.setString(3, nazAkce);
            ps.setArray(4, cisOrientacniIA);
            numInserted = ps.executeUpdate();
            connection.commit();
            if (numInserted == 0) {
                rollback(connection);
                log.log(Level.SEVERE, "Chyba při ukládání temínů. Nebyla SQLEception, ale uloženo nebylo.");
                ok = false;
            }

        } catch (SQLException e) {
            rollback(connection);
            log.log(Level.SEVERE, "Chyba při ukládání temínů.", e);
            ok = false;
        } finally {
            DBUtil.closePreparedStatement(ps);
            try {
                connection.close();
            } catch (SQLException ex) {
                log.log(Level.SEVERE, null, ex);
            }
        }
        return ok;
    }

    public TerminCisOri selectTermin(String nazUlice, String nazAkce, int[] cisOrientacni) {
        //TODO pool

        Connection connection = null;
        try {
            connection = ConnectionFactoryMyEdit.getConnection();
        } catch (SQLException ex) {
            log.log(Level.SEVERE, null, ex);
        }
        ResultSet rs;
        PreparedStatement ps = null;
        Array cisOrientacniSqlA = new IntArray(cisOrientacni);
        String query = "SELECT termin_cisteni FROM termin WHERE naz_ulice = ? AND naz_akce = ? AND  cis_orientacni = ?";
        TerminCisOri termin = null;
        try {
            ps = connection.prepareStatement(query);
            ps.setString(1, nazUlice);
            ps.setString(2, nazAkce);
            ps.setArray(3, cisOrientacniSqlA);
            rs = ps.executeQuery();
            while (rs.next()) {

                termin = new TerminCisOri();
                termin.setNazAkce(nazAkce);
                termin.setNazUlice(nazUlice);
                termin.setCisOrientacni(cisOrientacni);
                Timestamp terCisTS = rs.getTimestamp("termin_cisteni");
                Date terCis = new Date(terCisTS.getTime());
                termin.setTerCis(terCis);
                break;
            }
            connection.commit();
        } catch (SQLException e) {
            rollback(connection);
            log.log(Level.SEVERE, "Chyba při vybírání temínů.", e);
        } finally {
            DBUtil.closePreparedStatement(ps);
            try {
                connection.close();
            } catch (SQLException ex) {
                log.log(Level.SEVERE, null, ex);
            }
        }
        return termin;
    }

    public Collection<TerminCisOri> selectTerminsByNUliceNAkceTCis
            (String nazUlice, Date terCis, String nazAkce){
        //TODO pool
        Set<TerminCisOri> treeSetTermin = new TreeSet<TerminCisOri>();
        if (nazAkce == null || nazUlice == null || terCis == null) {

            return treeSetTermin;
        }
        Connection connection = null;
        try {
            connection = ConnectionFactoryMyEdit.getConnection();
        } catch (SQLException ex) {
            log.log(Level.SEVERE, null, ex);
        }

        ResultSet rs;
        PreparedStatement ps = null;
        String query = "SELECT  cis_orientacni FROM termin WHERE naz_ulice = ? AND  termin_cisteni = ? AND naz_akce = ?";
        Timestamp terCisTS = new Timestamp(terCis.getTime());

        try {
            ps = connection.prepareStatement(query);
            ps.setString(1, nazUlice);
            ps.setTimestamp(2, terCisTS);
            ps.setString(3, nazAkce);
            rs = ps.executeQuery();
            while (rs.next()) {
                TerminCisOri termin = new TerminCisOri();
                int[] cisOrientacni;
                termin.setNazAkce(nazAkce);
                termin.setNazUlice(nazUlice);
                termin.setTerCis(terCis);
                Array intArray = rs.getArray("cis_orientacni");
                String cisOriS = intArray.toString();
                String[] cisOriSA = cisOriS.substring(1, cisOriS.length()-1).split(",");
                cisOrientacni = new int[cisOriSA.length];
                for (int i = 0; i < cisOriSA.length; i++) {
                    cisOrientacni[i] = Integer.parseInt(cisOriSA[i]);
                }
                termin.setCisOrientacni(cisOrientacni);
                treeSetTermin.add(termin);

            }
            connection.commit();
        } catch (SQLException e) {
            rollback(connection);
            log.log(Level.SEVERE, "Chyba při vybírání temínů.", e);
        } finally {
            DBUtil.closePreparedStatement(ps);
            try {
                connection.close();
            } catch (SQLException ex) {
                log.log(Level.SEVERE, null, ex);
            }
        }
        return treeSetTermin;
    }

    /**
     * @param connection
     */
    protected void rollback(Connection connection) {
        try {
            connection.rollback();
        } catch (Exception e) {

            log.log(Level.FINEST, "Rollback se nezdařil", e);
        }
    }


}
